package com.metagurukul.metagranth.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.metagurukul.metagranth.model.common.connection.ConnectionPoolFactory;
import com.metagurukul.metagranth.model.common.dbutil.Util;
import com.metagurukul.metagranth.vo.Answer;
import com.metagurukul.metagranth.vo.Question;
import com.metagurukul.metagranth.vo.QuestionTag;
import com.metagurukul.metagranth.vo.Tag;
import com.metagurukul.metagranth.vo.User;

public class CommonJUnit {
	
/**
 * 
 * @param userId user_identification_no of question who posted the question
 * @return ArrayList of Questions posted by this user.
 * @throws SQLException 
 */
	
	
	public static ArrayList<Question> retrieveTestQuestions(int userId) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "select * from METACUBEGRANTH_QUESTION where USER_IDENTIFICATION_NUMBER=?";
		PreparedStatement ps = null;
		ArrayList<Question> listOfQuestions = new ArrayList<Question>();
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			listOfQuestions = processResultSetForQuestion(rs);
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return listOfQuestions;
	}
	
	
	
	/**
	 * 
	 * @param userId user_identification_no of answer who posted the answer
	 * @return ArrayList of answers posted by this user.
	 * @throws SQLException 
	 */
	public static ArrayList<Answer> retrieveTestAnswers(int userId) throws SQLException {
		// make a connection pool factory and take a connection.
		
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "select * from METACUBEGRANTH_ANSWER where USER_IDENTIFICATION_NUMBER=?";
		PreparedStatement ps = null;
		ArrayList<Answer> listAnswers = new ArrayList<Answer>();
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			listAnswers = processResultSetForAnswer(rs);
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return listAnswers;
	}
	
	/**
	 * 
	 * @param tag is the tagName of tag which is to find. 
	 * @return list of all the tags whose tagName is given tagName.
	 * @throws SQLException 
	 */
	
	public static ArrayList<Tag> retrieveTestTag(String tag) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "select * from METACUBEGRANTH_TAG where TAG_NAME=?";
		PreparedStatement ps = null;
		ArrayList<Tag> listOfTag = new ArrayList<Tag>();
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, tag);
			ResultSet rs = ps.executeQuery();
			listOfTag = processResultSetForTag(rs);
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return listOfTag;
	}		
	/**
	 * 
	 * @param userId is USER_IDENTIFICATION_NUMBER of user who posted the question and tags
	 * and mapping of those is in question tag table
	 * @return list of question tag 
	 * @throws SQLException 
	 */

	public static ArrayList<QuestionTag> retrieveTestQuestion_Tag(int userId) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "select * from METACUBEGRANTH_QUESTION_TAG where QUESTION_IDENTIFICATION_NUMBER "
			+ "in (select QUESTION_IDENTIFICATION_NUMBER from METACUBEGRANTH_QUESTION where USER_IDENTIFICATION_NUMBER=?)";
		PreparedStatement ps = null;
		ArrayList<QuestionTag> listOfQuestionTag = new ArrayList<QuestionTag>();
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();
			listOfQuestionTag = processResultSetForQuestionTag(rs);
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return listOfQuestionTag;
	}
/**
 * 
 * @param question is the object of Question vo class which is to insert into database.
 * @return int status-value--->row updated
 * @throws SQLException 
 */
	public static int insertTestData(Question question) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String questionTitle = question.getTitle();
		String questionDescription = question.getQuestion();
		int uid = question.getUserId();
		String query = "INSERT INTO metacubegranth_question (TITLE,QUESTION,USER_IDENTIFICATION_NUMBER) VALUES(?,?,?)";
		PreparedStatement ps = null;
		int generatedQuestionId = 0;
		try {

			ps = con.prepareStatement(query, ps.RETURN_GENERATED_KEYS);
			/* set variable in prepared statement */
			ps.setString(1, questionTitle);
			ps.setString(2, questionDescription);
			ps.setInt(3, uid);
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				generatedQuestionId = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw e;
			} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return generatedQuestionId;
	}
	/**
	 * 
	 * @param tableName is the table name in which we are deleting data
	 * @param userId is the USER_IDENTIFICATION_NUMBER for which we have to delete data.
	 * @return int status..value=row deletd
	 * @throws SQLException 
	 *                    
	 */
	public static int deleteTestData(String tableName, int userId) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "DELETE from " + tableName
		+ " where USER_IDENTIFICATION_NUMBER=?";
		PreparedStatement ps = null;
		int status = -1;
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			status = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return status;
	}
/**
 * 
 * @param userId is USER_IDENTIFICATION_NUMBER who insert question and tags in corresponding tables
 * @return status..value=row count for deletion 
 * @throws SQLException 
 */
	public static int deleteTestDataFromQuestion_Tags(int userId) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "DELETE from METACUBEGRANTH_QUESTION_TAG where "
			+ " QUESTION_IDENTIFICATION_NUMBER in (select QUESTION_IDENTIFICATION_NUMBER from "
			+ " METACUBEGRANTH_QUESTION where USER_IDENTIFICATION_NUMBER = ?)";

		PreparedStatement ps = null;
		int status = -1;
		try {
			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setInt(1, userId);
			status = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return status;
	}

	/**
	 * 
	 * @param tagName is name of tag which is to be deleted.
	 * @return status..value=row count for deletion
	 * @throws SQLException 
	 */
	public static int deleteTestDataFromTags(String tagName) throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String query = "DELETE from METACUBEGRANTH_TAG " + " where TAG_NAME=?";

		PreparedStatement ps = null;
		int status = -1;
		try {

			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, tagName);
			status = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return status;
	}
/**
 * 
 * @return status ..value=1 for success other wise failure.
 * @throws SQLException 
 */
	public static int deleteTestUser() throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		// query to make changes in database
		String userName = "test@metagurukul.com";
		String query = "Delete from metacubegranth_user where USER_NAME=? ";

		PreparedStatement ps = null;
		int status = -1;
		try {

			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, userName);
			status = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return status;

	}
/**
 * 
 * @return User_identification_no of created user
 * @throws SQLException 
 */
	public static int createTestUser() throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		String name = "test";
		String userName = "test@metagurukul.com";
		String password = "testpass";
		int age = 25;
		// query to make changes in database
		String query = "INSERT INTO metacubegranth_user (USER_NAME,PASSWORD,NAME,AGE) VALUES (?,?,?,?) ";
		PreparedStatement ps = null;
		int uid = -1;
		try {
			ps = con.prepareStatement(query, ps.RETURN_GENERATED_KEYS);
			/* set variable in prepared statement */
			ps.setString(1, userName);
			ps.setString(2, password);
			ps.setString(3, name);
			ps.setInt(4, age);
			uid = ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return uid;

	}
	/**
	 * 
	 * @return User_identification_no of created user
	 * @throws SQLException 
	 */
		public static int createTestAdminUser() throws SQLException {
			// make a connection pool factory and take a connection.
			ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
			Connection con = cp.checkOut();
			String name = "test";
			String userName = "test@metagurukul.com";
			String password = "testpass";
			int age = 25;
			int type=1;
			// query to make changes in database
			String query = "INSERT INTO metacubegranth_user (USER_NAME,PASSWORD,NAME,AGE,TYPE) VALUES (?,?,?,?,?) ";
			PreparedStatement ps = null;
			int uid = -1;
			try {
				ps = con.prepareStatement(query, ps.RETURN_GENERATED_KEYS);
				/* set variable in prepared statement */
				ps.setString(1, userName);
				ps.setString(2, password);
				ps.setString(3, name);
				ps.setInt(4, age);
				ps.setInt(5, type);
				uid = ps.executeUpdate();
			} catch (SQLException e) {
				throw e;
			} finally {// close prepareStatement and Check in into connectionFactory
				Util.closePreparedStatement(ps);
				cp.checkIn(con);
			}
			return uid;

		}
	/**
	 * 
	 * @return user object of class User.it returns the user who is test user actually.
	 * @throws SQLException 
	 */
	public static User selectTestUser() throws SQLException {
		// make a connection pool factory and take a connection.
		ConnectionPoolFactory cp=ConnectionPoolFactory.getInstance();
		Connection con = cp.checkOut();
		String userName = "test@metagurukul.com";
		// query to make changes in database
		String query = "select * from metacubegranth_user where USER_NAME = ? ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		User testUser = new User();
		try {

			ps = con.prepareStatement(query);
			/* set variable in prepared statement */
			ps.setString(1, userName);
			rs = ps.executeQuery();
			try {// collect user data
				if (rs.next()) {
					int uid = rs.getInt(1);
					testUser.setUid(uid);
					String username = rs.getString(2);
					testUser.setUsername(username);
					String password = rs.getString(3);
					testUser.setPassword(password);
					String name = rs.getString(4);
					testUser.setName(name);
					int age = rs.getInt(5);
					testUser.setAge(age);
					Date date = rs.getDate(6);
					testUser.setLastlogindate(date);
					int type = rs.getInt(7);
					testUser.setType(type);
				}
			} catch (SQLException e) {
				throw e;
			}

		} catch (SQLException e) {
			throw e;
		} finally {// close prepareStatement and Check in into connectionFactory
			Util.closePreparedStatement(ps);
			cp.checkIn(con);
		}
		return testUser;
	}

	/**
	 * 
	 * @param rs is result set whose values are to be filled to qustions
	 * @return arraylist of questions
	 * @throws SQLException 
	 */
	private static ArrayList<Question> processResultSetForQuestion(ResultSet rs) throws SQLException {
		ArrayList<Question> questionsList = new ArrayList<Question>();
		Question question = null;
		try {//iterate result set till data in result set
			while (rs.next()) {
				//make a new question
				question = new Question();
				//get question id 
				int qid = rs.getInt(1);
				//set question id
				question.setQuesId(qid);
				//get title
				String title = rs.getString(2);
				//set title
				question.setTitle(title);
				//get questiion description
				String ques = rs.getString(3);
				//set question description
				question.setQuestion(ques);
				//get user id
				int uid = rs.getInt(4);
				//set user id
				question.setUserId(uid);
				//get timestamp 
				Timestamp dop = rs.getTimestamp(5);
				//set date of post
				question.setDateOfPost(dop);
				//close date get
				Date closedate = rs.getDate(6);
				//set close date
				question.setCloseDate(closedate);
				//get boolean isclose
				boolean isclose = rs.getBoolean(7);
				//set boolean is closed
				question.setIsClosed(isclose);
				//add in list
				questionsList.add(question);
			}
		} catch (SQLException e) {
			throw e;
		}
		//return list
		return questionsList;
	}

private static ArrayList<Tag> processResultSetForTag(ResultSet rs) throws SQLException {
		
	ArrayList<Tag> tagList=new ArrayList<Tag>();
	Tag tag=null;
	try {
		while (rs.next()) {
			tag = new Tag();
			int tid = rs.getInt(1);
			tag.setTagid(tid);
			String tagName = rs.getString(2);
			tag.setTagName(tagName);
			tagList.add(tag);
		}
	} catch (SQLException e) {
		throw e;
	}
	return tagList;
		
	}
private static ArrayList<QuestionTag> processResultSetForQuestionTag(
		ResultSet rs) throws SQLException {
	ArrayList<QuestionTag> QuestionTagList=new ArrayList<QuestionTag>();
	QuestionTag questionTag=null;
	try {
		while (rs.next()) {
			questionTag = new QuestionTag();
			int tid = rs.getInt(1);
			questionTag.setTagid(tid);
			int qid = rs.getInt(2);
			questionTag.setQid(qid);
			QuestionTagList.add(questionTag);
		}
	} catch (SQLException e) {
		throw e;
	}
	return QuestionTagList;
	
}

	private static ArrayList<Answer> processResultSetForAnswer(ResultSet rs)
			throws SQLException {
	ArrayList<Answer> AnswerList=new ArrayList<Answer>();
	Answer answer=null;
	try {
		while (rs.next()) {
			answer = new Answer();
			int aid = rs.getInt(1);
			answer.setAid(aid);
			int qid = rs.getInt(2);
			answer.setQid(qid);
			int uid = rs.getInt(3);
			answer.setUid(uid);
			String ans = rs.getString(4);
			answer.setAnswer(ans);
			AnswerList.add(answer);
		}
	} catch (SQLException e) {
		throw e;
	}
	return AnswerList;
	
}
}
